import os
from datetime import datetime
from flask import Blueprint, request, jsonify, current_app, url_for
from flask_jwt_extended import jwt_required, get_jwt_identity
from werkzeug.utils import secure_filename
from app.models.user import User
from app.models.product import Product
from main import db
import json
from sqlalchemy import text

product_bp = Blueprint("product", __name__)


def allowed_file(filename):
    return "." in filename and filename.rsplit(".", 1)[1].lower() in {
        "png",
        "jpg",
        "jpeg",
        "gif",
    }


@product_bp.route("/upload_image", methods=["POST"])
@jwt_required()
def upload_image():
    """上传图片到服务器"""

    if "file" not in request.files:
        return jsonify({"error": "No file part"}), 400

    file = request.files["file"]

    if file.filename == "":
        return jsonify({"error": "No selected file"}), 400

    if file and allowed_file(file.filename):
        filename = secure_filename(file.filename)
        # 生成一个时间戳前缀，避免文件名冲突
        timestamp = datetime.now().strftime("%Y%m%d%H%M%S")
        filenameWithTimestamp = f"{timestamp}_{filename}"

        # 确保上传目录存在
        uploadDir = os.path.join(current_app.config["UPLOAD_FOLDER"], "products")
        if not os.path.exists(uploadDir):
            os.makedirs(uploadDir)

        filepath = os.path.join(uploadDir, filenameWithTimestamp)
        file.save(filepath)

        # 返回文件的URL路径
        fileUrl = f"/uploads/products/{filenameWithTimestamp}"
        return jsonify({"url": fileUrl})

    return jsonify({"error": "File type not allowed"}), 400


@product_bp.route("/create", methods=["POST"])
@jwt_required()
def create_product():
    userId = get_jwt_identity()
    user = User.query.get_or_404(userId)

    if not user.is_employee() and not user.is_admin():
        return jsonify({"error": "Unauthorized"}), 403

    # 检查是否为JSON数据
    if not request.is_json:
        return jsonify({"error": "Expected JSON data"}), 400

    data = request.get_json()

    # 验证必填字段
    if not all(
        [
            data.get("name"),
        ]
    ):
        return (
            jsonify(
                {
                    "error": "Missing required fields (category, warehouse, name, preview_image_url, date)"
                }
            ),
            400,
        )

    # 检查SKUs是否已存在
    skus = data.get("skus")

    # 创建产品
    product = Product(
        category=data["category"],
        name=data["name"],
        preview_image_url=data["preview_image_url"],
        warehouse=data["warehouse"] if data["warehouse"] else "电商",
        date=data["date"],
        # 配件相关字段
        metalMaterial=data.get("metalMaterial"),
        jadeSize=float(data.get("jadeSize")) if data.get("jadeSize") else None,
        jadeMaterial=data.get("jadeMaterial"),
        accessoryOthers=data.get("accessoryOthers"),
        # 单选字段
        pearlMaterial=data.get("pearlMaterial"),
        pearlRoundness=data.get("pearlRoundness"),
        pearlClarity=data.get("pearlClarity"),
        # 备注
        remark=data.get("remark"),
        # JSON字段
        channel=data.get("channel"),
        skus=skus,
        # 创建者
        created_by=userId,
    )

    db.session.add(product)
    db.session.commit()

    return jsonify(product.to_dict()), 201


@product_bp.route("/list", methods=["GET"])
@jwt_required()
def list_products():
    userId = get_jwt_identity()
    user = User.query.get_or_404(userId)

    # 构建查询 - 只基于产品级别的属性
    query = Product.query.filter_by(deleted=False)

    # 如果是客户，只显示电商仓库的产品
    if user.is_customer():
        query = query.filter_by(warehouse="电商")

    # 应用筛选条件 - 类别
    categories = request.args.get("categories")
    if categories:
        categoryList = categories.split(",")
        query = query.filter(Product.category.in_(categoryList))

    # 搜索条件 - 名称
    searchName = request.args.get("name")
    if searchName:
        query = query.filter(Product.name.ilike(f"%{searchName}%"))

    # SKU编码搜索
    skuCode = request.args.get("skuCode")
    if skuCode:
        # 在JSON数组中搜索特定skuCode
        query = query.filter(
            text("JSON_SEARCH(skus, 'one', :skuCode) IS NOT NULL")
        ).params(skuCode=skuCode)

    # 创建日期筛选
    createdDate = request.args.get("createdDate")
    if createdDate:
        # 假设日期格式为YYYY-MM-DD
        query = query.filter(db.func.date(Product.date) == createdDate)

    # 珍珠属性 - 产品级属性
    pearlRoundness = request.args.get("pearlRoundness")
    if pearlRoundness:
        query = query.filter(Product.pearlRoundness == pearlRoundness)

    pearlClarity = request.args.get("pearlClarity")
    if pearlClarity:
        query = query.filter(Product.pearlClarity == pearlClarity)

    pearlMaterial = request.args.get("pearlMaterial")
    if pearlMaterial:
        query = query.filter(Product.pearlMaterial == pearlMaterial)

    # 金属材质 - 产品级属性
    metalMaterial = request.args.get("metalMaterial")
    if metalMaterial:
        query = query.filter(Product.metalMaterial == metalMaterial)

    # 玉石相关属性 - 产品级属性
    jadeMaterial = request.args.get("jadeMaterial")
    if jadeMaterial:
        query = query.filter(Product.jadeMaterial.ilike(f"%{jadeMaterial}%"))

    minJadeSize = request.args.get("minJadeSize")
    maxJadeSize = request.args.get("maxJadeSize")
    if minJadeSize:
        query = query.filter(Product.jadeSize >= float(minJadeSize))
    if maxJadeSize:
        query = query.filter(Product.jadeSize <= float(maxJadeSize))

    # 配件和备注 - 产品级属性
    accessories = request.args.get("accessories")
    if accessories:
        query = query.filter(Product.accessoryOthers.ilike(f"%{accessories}%"))

    remarks = request.args.get("remarks")
    if remarks:
        query = query.filter(Product.remark.ilike(f"%{remarks}%"))

    # 获取所有符合条件的产品
    products = query.all()

    # 提取筛选参数 - SKU级属性
    minPrice = request.args.get("minPrice")
    maxPrice = request.args.get("maxPrice")
    minLength = request.args.get("minLength")
    maxLength = request.args.get("maxLength")
    minDiameter = request.args.get("minDiameter")
    maxDiameter = request.args.get("maxDiameter")
    minPearlSize = request.args.get("minPearlSize")
    maxPearlSize = request.args.get("maxPearlSize")
    pearlColor = request.args.get("pearlColor")

    # 如果有SKU级别的筛选条件，在内存中过滤产品
    filtered_products = []
    for product in products:
        # 检查产品是否有skus属性
        if not hasattr(product, "skus") or not product.skus:
            filtered_products.append(product)
            continue

        # 检查该产品的任何SKU是否满足所有筛选条件
        sku_matches = False
        for sku in product.skus:
            matches_all_criteria = True

            # 价格筛选
            if minPrice and (
                "salePrice" not in sku or float(sku.get("salePrice", 0)) < float(minPrice)
            ):
                matches_all_criteria = False
            if maxPrice and (
                "salePrice" not in sku or float(sku.get("salePrice", 0)) > float(maxPrice)
            ):
                matches_all_criteria = False

            # 长度筛选
            if minLength and (
                "length" not in sku or float(sku.get("length", 0)) < float(minLength)
            ):
                matches_all_criteria = False
            if maxLength and (
                "length" not in sku or float(sku.get("length", 0)) > float(maxLength)
            ):
                matches_all_criteria = False

            # 直径筛选
            if minDiameter and (
                "diameter" not in sku
                or float(sku.get("diameter", 0)) < float(minDiameter)
            ):
                matches_all_criteria = False
            if maxDiameter and (
                "diameter" not in sku
                or float(sku.get("diameter", 0)) > float(maxDiameter)
            ):
                matches_all_criteria = False

            # 珍珠大小筛选
            if minPearlSize and (
                "pearlSize" not in sku
                or float(sku.get("pearlSize", 0)) < float(minPearlSize)
            ):
                matches_all_criteria = False
            if maxPearlSize and (
                "pearlSize" not in sku
                or float(sku.get("pearlSize", 0)) > float(maxPearlSize)
            ):
                matches_all_criteria = False

            # 珍珠颜色筛选
            if pearlColor and (
                "pearlColor" not in sku or sku.get("pearlColor") != pearlColor
            ):
                matches_all_criteria = False

            # 如果至少有一个SKU满足所有条件，则产品匹配
            if matches_all_criteria:
                sku_matches = True
                break

        # 如果有SKU级筛选条件，而且产品有匹配的SKU，添加到结果中
        if (
            minPrice
            or maxPrice
            or minLength
            or maxLength
            or minDiameter
            or maxDiameter
            or minPearlSize
            or maxPearlSize
            or pearlColor
        ):
            if sku_matches:
                filtered_products.append(product)
        else:
            # 如果没有SKU级筛选条件，则所有产品都匹配
            filtered_products.append(product)

    # 分页处理
    page = request.args.get("page", 1, type=int)
    perPage = request.args.get("size", 20, type=int)

    # 计算总数和分页
    total = len(filtered_products)
    total_pages = (total + perPage - 1) // perPage  # 向上取整

    # 截取当前页的数据
    start_idx = (page - 1) * perPage
    end_idx = min(start_idx + perPage, total)
    paged_products = filtered_products[start_idx:end_idx]

    return jsonify(
        {
            "data": {
                "items": [product.to_dict() for product in paged_products],
                "total": total,
                "pages": total_pages,
                "currentPage": page,
            }
        }
    )


@product_bp.route("/detail/<int:product_id>", methods=["GET"])
@jwt_required()
def get_product(product_id):
    userId = get_jwt_identity()
    user = User.query.get_or_404(userId)

    product = Product.query.get_or_404(product_id)

    # 检查商品是否已删除
    if product.deleted:
        return jsonify({"error": "Product not found"}), 404

    return jsonify({"data": product.to_dict()})


@product_bp.route("/update/<int:product_id>", methods=["PUT"])
@jwt_required()
def update_product(product_id):
    userId = get_jwt_identity()
    user = User.query.get_or_404(userId)

    if not user.is_employee() and not user.is_admin():
        return jsonify({"error": "Unauthorized"}), 403

    product = Product.query.get_or_404(product_id)

    # 检查是否为JSON数据
    if not request.is_json:
        return jsonify({"error": "Expected JSON data"}), 400

    data = request.get_json()

    # 更新基本属性
    if "category" in data:
        product.category = data["category"]
    if "name" in data:
        product.name = data["name"]
    if "warehouse" in data:
        product.warehouse = data["warehouse"]
    if "date" in data:
        product.date = data["date"]

    # 更新预览图
    if "preview_image_url" in data:
        product.preview_image_url = data["preview_image_url"]
    # 更新配件相关字段
    if "metalMaterial" in data:
        product.metalMaterial = data["metalMaterial"]
    if "jadeSize" in data:
        product.jadeSize = float(data["jadeSize"]) if data["jadeSize"] else None
    if "jadeMaterial" in data:
        product.jadeMaterial = data["jadeMaterial"]
    if "accessoryOthers" in data:
        product.accessoryOthers = data["accessoryOthers"]

    # 更新单选字段
    if "pearlMaterial" in data:
        product.pearlMaterial = data["pearlMaterial"]
    if "pearlRoundness" in data:
        product.pearlRoundness = data["pearlRoundness"]
    if "pearlClarity" in data:
        product.pearlClarity = data["pearlClarity"]

    # 更新备注
    if "remark" in data:
        product.remark = data["remark"]

    # 更新渠道信息 (JSON)
    if "channel" in data:
        product.channel = data["channel"]

    # 更新SKUs (JSON)
    if "skus" in data:
        # 更新SKUs
        product.skus = data["skus"]

    product.updated_at = datetime.now()
    db.session.commit()
    return jsonify(product.to_dict())


@product_bp.route("/delete/<int:product_id>", methods=["DELETE"])
@jwt_required()
def delete_product(product_id):
    userId = get_jwt_identity()
    user = User.query.get_or_404(userId)

    if not user.is_employee() and not user.is_admin():
        return jsonify({"error": "Unauthorized"}), 403

    product = Product.query.get_or_404(product_id)

    try:
        # 软删除产品，而不是实际删除
        product.deleted = True
        product.updated_at = datetime.now()
        db.session.commit()
        return jsonify({"message": "Product deleted successfully"})
    except Exception as e:
        db.session.rollback()
        return jsonify({"error": f"Failed to delete product: {str(e)}"}), 500


@product_bp.route("/generate-sku-codes", methods=["POST"])
@jwt_required()
def generate_sku_codes():
    """
    生成SKU编码

    自动编号格式：2位类别字母+6位数字日期+2位数字序号+1位规格序号
    产品类别代号：ES耳饰、XL项链、SL手链、JZ戒指、XZ胸针、QT其他
    日期：6位数年月日
    序号：2位数，每个产品类别分别排序，第一个序号是01，每日刷新重新从01开始
    规格序号：1位数，第一个序号是1
    """
    userId = get_jwt_identity()
    user = User.query.get_or_404(userId)

    # 验证用户权限（管理员或员工可以生成SKU）
    if not user.is_admin() and not user.is_employee():
        return jsonify({"error": "Unauthorized"}), 403

    # 检查请求内容
    if not request.is_json:
        return jsonify({"error": "Missing JSON in request"}), 400

    data = request.get_json()
    print(f"Received data: {data}")

    # 获取请求参数
    categoryCode = data.get("categoryCode")  # 例如：ES, XL, SL, JZ, XZ, QT
    dateStr = data.get("dateStr")  # 格式：YYMMDD
    skuCount = data.get("skuCount", 0)  # 规格数量
    productId = data.get("productId")  # 产品ID，可选



    productSeq = 0
    skuMax = 0
    
    if productId:
        # 如果产品ID存在，说明是修改产品信息新增sku，则从已有的sku中提取序号
        product = Product.query.get_or_404(productId)
        if product.deleted:
            return jsonify({"error": "Product not found"}), 404
        if product.skus:
            productSeq = int(product.skus[0]['skuCode'][8:10])
            
            for sku in product.skus:
                skuMax = max(skuMax, int(sku['skuCode'][10:11]))
            
    else:   
        # 如果产品ID不存在，说明是新增产品，则按照规则生成序号
        
        
        sqlQuery = text(
            """
        SELECT p.*, JSON_EXTRACT(p.skus, '$[*].skuCode') as sku_codes
        FROM Product p
        WHERE JSON_SEARCH(p.skus, 'one', :pattern) IS NOT NULL;
        """
        )

        pattern = f"{categoryCode}{dateStr}%"
        result = db.session.execute(
            sqlQuery, {"dateStr": dateStr, "pattern": pattern}
        ).fetchall()

        # 解析结果，提取最大序号
        if result:
            for row in result:
                if row.sku_codes:
                    # 尝试从每个SKU代码中提取序号部分（第9-10位）
                    skuCodes = json.loads(row.sku_codes)
                    for skuCode in skuCodes:
                        if skuCode:
                            seqNum = int(skuCode[8:10])
                            productSeq = max(productSeq, seqNum)
        
        productSeq = productSeq + 1

    # 生成新的SKU编码列表
    skuCodes = []
    for i in range(skuMax+1, skuMax + skuCount + 1):
        # 构建SKU: 2位类别 + 6位日期 + 2位产品序号 + 1位规格序号
        skuCode = f"{categoryCode}{dateStr}{productSeq:02d}{i}"
        skuCodes.append(skuCode)

    # 返回生成的SKU代码
    return jsonify({"data": skuCodes})


@product_bp.route("/restore/<int:product_id>", methods=["POST"])
@jwt_required()
def restore_product(product_id):
    """恢复已删除的商品"""
    userId = get_jwt_identity()
    user = User.query.get_or_404(userId)

    if not user.is_employee() and not user.is_admin():
        return jsonify({"error": "Unauthorized"}), 403

    product = Product.query.get_or_404(product_id)

    try:
        # 恢复已删除的商品
        product.deleted = False
        product.updated_at = datetime.now()
        db.session.commit()
        return jsonify(
            {"message": "Product restored successfully", "data": product.to_dict()}
        )
    except Exception as e:
        db.session.rollback()
        return jsonify({"error": f"Failed to restore product: {str(e)}"}), 500


@product_bp.route("/deleted", methods=["GET"])
@jwt_required()
def list_deleted_products():
    """查看已删除的商品列表"""
    userId = get_jwt_identity()
    user = User.query.get_or_404(userId)

    if not user.is_employee() and not user.is_admin():
        return jsonify({"error": "Unauthorized"}), 403

    # 构建查询 - 只查询已删除的产品
    query = Product.query.filter_by(deleted=True)

    # 分页处理
    page = request.args.get("page", 1, type=int)
    perPage = request.args.get("size", 20, type=int)

    # 按更新时间降序排序
    query = query.order_by(Product.updated_at.desc())

    # 执行分页查询
    pagination = query.paginate(page=page, per_page=perPage)
    products = pagination.items

    return jsonify(
        {
            "data": {
                "items": [product.to_dict() for product in products],
                "total": pagination.total,
                "pages": pagination.pages,
                "currentPage": page,
            }
        }
    )
